Owner

dbo

Creation Date

10-07-2003 10:31:36

Remarks

Columns

Name	Type	Length	IsNullable	Collation
ShipName	nvarchar	80		SQL_Latin1_General_CP1_CI_AS
ShipName	sysname	80		SQL_Latin1_General_CP1_CI_AS
ShipAddress	nvarchar	120		SQL_Latin1_General_CP1_CI_AS
ShipAddress	sysname	120		SQL_Latin1_General_CP1_CI_AS
ShipCity	nvarchar	30		SQL_Latin1_General_CP1_CI_AS
ShipCity	sysname	30		SQL_Latin1_General_CP1_CI_AS
ShipRegion	nvarchar	30		SQL_Latin1_General_CP1_CI_AS
ShipRegion	sysname	30		SQL_Latin1_General_CP1_CI_AS
ShipPostalCode	nvarchar	20		SQL_Latin1_General_CP1_CI_AS
ShipPostalCode	sysname	20		SQL_Latin1_General_CP1_CI_AS
ShipCountry	nvarchar	30		SQL_Latin1_General_CP1_CI_AS
ShipCountry	sysname	30		SQL_Latin1_General_CP1_CI_AS
CustomerID	nchar	10		SQL_Latin1_General_CP1_CI_AS
CustomerName	nvarchar	80		SQL_Latin1_General_CP1_CI_AS
CustomerName	sysname	80		SQL_Latin1_General_CP1_CI_AS
Address	nvarchar	120		SQL_Latin1_General_CP1_CI_AS
Address	sysname	120		SQL_Latin1_General_CP1_CI_AS
City	nvarchar	30		SQL_Latin1_General_CP1_CI_AS
City	sysname	30		SQL_Latin1_General_CP1_CI_AS
Region	nvarchar	30		SQL_Latin1_General_CP1_CI_AS
Region	sysname	30		SQL_Latin1_General_CP1_CI_AS
PostalCode	nvarchar	20		SQL_Latin1_General_CP1_CI_AS
PostalCode	sysname	20		SQL_Latin1_General_CP1_CI_AS
Country	nvarchar	30		SQL_Latin1_General_CP1_CI_AS
Country	sysname	30		SQL_Latin1_General_CP1_CI_AS
Salesperson	nvarchar	62		SQL_Latin1_General_CP1_CI_AS
Salesperson	sysname	62		SQL_Latin1_General_CP1_CI_AS
OrderID	int	4
OrderDate	datetime	8
RequiredDate	datetime	8
ShippedDate	datetime	8
ShipperName	nvarchar	80		SQL_Latin1_General_CP1_CI_AS
ShipperName	sysname	80		SQL_Latin1_General_CP1_CI_AS
ProductID	int	4
ProductName	nvarchar	80		SQL_Latin1_General_CP1_CI_AS
ProductName	sysname	80		SQL_Latin1_General_CP1_CI_AS
UnitPrice	money	8
Quantity	smallint	2
Discount	real	4
ExtendedPrice	money	8
Freight	money	8

View Definition

create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName + ' ' + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, 
	"Order Details".Discount, 
	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) 
		ON Products.ProductID = "Order Details".ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia